﻿Imports System.Data.SqlClient
Imports Abyx.SVCMS.Library
Imports Abyx.SVCMS.Library.DB
Imports System.Xml.Serialization
Imports System.IO

Public Class SysManager
    Inherits ManagerBase

#Region "Constructors"
    Public Sub New(ByVal LMuser As UserType)
        MyBase.New(LMuser)
    End Sub
#End Region

#Region "Config"
    Shared Function Config_Get(moduleName As String, configName As String) As ConfigType
        Dim config As New ConfigType

        Dim cmd As New SqlCommand("SELECT * FROM SYS_Config " & _
                                  "WHERE ModuleName='" & moduleName & "' " & _
                                  "     AND ConfigName='" & configName & "'")
        Dim dt As DataTable = Nothing
        Using con As New SqlConnection(Utility.GetConnectionString)
            Try
                con.Open()
                cmd.Connection = con
                dt = Utility.GetList(cmd)
                If dt Is Nothing Then
                    Throw New RequestedRecordDoesNotExistException(AppConstants.Error_Message_Sql_No_Results)
                Else
                    If dt.Rows.Count < 1 Then
                        Throw New RequestedRecordDoesNotExistException(AppConstants.Error_Message_Sql_No_Results)
                    Else
                        Dim firstRow As DataRow = dt.Rows(0)
                        With config
                            .Connection = con
                            .Load(firstRow)
                        End With
                    End If
                End If
            Catch ex As RequestedRecordDoesNotExistException
                Throw ex
            Catch ex As Exception
                Throw ex
            Finally
                con.Close()
            End Try
        End Using

        Return config
    End Function
    Shared Sub Config_Set(moduleName As String, configName As String, ConfigValue As String)

        Dim config As New ConfigType
        Dim cmd As New SqlCommand("UPDATE SYS_Config " & _
                                  "SET ConfigValue='" & ConfigValue & "' " & _
                                  "WHERE ModuleName='" & moduleName & "' " & _
                                  "     AND ConfigName='" & configName & "'")
        Using con As New SqlConnection(Utility.GetConnectionString)
            Try
                con.Open()
                cmd.CommandType = CommandType.Text
                cmd.Connection = con
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                Throw ex
            Finally
                con.Close()
            End Try
        End Using

    End Sub

#End Region

#Region "License Management"
    Public Function LicenseInformation_Load(LicenseTypeObject As LicenseType) As Boolean

        Dim cmd As New SqlCommand("SELECT TOP 1 * FROM SYS_License")
        Dim dt As DataTable = Nothing
        Using con As New SqlConnection(Utility.GetConnectionString)
            Try
                con.Open()
                cmd.Connection = con
                dt = Utility.GetList(cmd)

            Catch ex As Exception
                Throw ex
            Finally
                con.Close()
            End Try
        End Using
        If Not dt Is Nothing Then
            If dt.Rows.Count > 0 Then
                LicenseTypeObject.Load(dt.Rows(0))
            Else
                Throw New InvalidLicenseInformationException("Lincense information does not exist in database")
            End If
        Else
            Throw New InvalidLicenseInformationException("Lincense information does not exist in database")
        End If
        Return True
    End Function
    Shared Function License_Read() As LicenseInfoType

        'Read from config
        Dim cmd As New SqlCommand("SELECT TOP 1 * FROM SYS_Config WHERE [ModuleName]='System' AND [ConfigName]='License'")
        Dim dt As DataTable = Nothing
        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            cmd.Connection = con
            dt = Utility.GetList(cmd)
            con.Close()
        End Using
        Dim encryptedLicense As String = Nothing
        If Not dt Is Nothing Then
            If dt.Rows.Count > 0 Then
                encryptedLicense = dt.Rows(0).Item("ConfigValue")
            Else
                Throw New InvalidLicenseInformationException("Lincense information does not exist in database")
            End If
        Else
            Throw New InvalidLicenseInformationException("Lincense information does not exist in database")
        End If

        'Decrypt config data
        Dim decryptedLicense() As Byte = Utility.DecryptData_3DES_Bytes(encryptedLicense)

        'Deserialize decrypted data into type
        Dim x As New XmlSerializer(GetType(LicenseInfoType))
        Dim ms As New MemoryStream(decryptedLicense)
        Dim license As LicenseInfoType = x.Deserialize(ms)

        'Return the results
        Return license

    End Function
    Shared Sub License_Write(LicenseInfoObject As LicenseInfoType)

        'Serialize object
        Dim x As New XmlSerializer(GetType(LicenseInfoType))
        Dim ms As New MemoryStream
        x.Serialize(ms, LicenseInfoObject)
        Dim serializedData() As Byte = ms.GetBuffer

        'Encrypt serialized data
        Dim encryptedData As String = Utility.EncriptData_3DES(serializedData)

        'Write into config
        Dim cmd As New SqlCommand
        cmd.CommandText = "SELECT COUNT(*) FROM [SYS_Config] WHERE [ModuleName]='System' AND [ConfigName]='License'"
        cmd.CommandType = CommandType.Text
        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            cmd.Connection = con
            If Utility.GetRecordCount(cmd) > 0 Then
                'Update license
                cmd.CommandText = "UPDATE [SYS_Config] " & _
                                  "SET [ConfigValue] = @License ,[LMDate] = GETDATE() " & _
                                  "WHERE [ModuleName]='System' AND [ConfigName]='License'"
                cmd.Parameters.AddWithValue("@License", encryptedData)
                cmd.ExecuteNonQuery()
            Else
                'Insert license
                cmd.CommandText = "INSERT INTO [SYS_Config] " & _
                                  "VALUES (NEWID(),'System','License',@License,NULL,GETDATE())"
                cmd.Parameters.AddWithValue("@License", encryptedData)
                cmd.ExecuteNonQuery()
            End If
            con.Close()
        End Using

    End Sub
#End Region

#Region "System Settings"
    Public Function Settings_Read() As SettingsType
        Dim s As SettingsType = Utility.Settings_Read
        Return s
    End Function
    Public Sub Settings_Save(Settings As SettingsType)
        Utility.Settings_Write(Settings)
    End Sub
#End Region

#Region "NumberSerquence"
    Shared Function Get_SequenceNo(con As SqlConnection, _
                               trans As SqlTransaction, _
                               NumberingName As SequenceNumeringNamesEnum, _
                               Optional Increment As Boolean = False) As String
        Dim res As String = ""
        Dim cmd As New SqlCommand
        cmd.Connection = con
        cmd.Transaction = trans
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT * FROM [SYS_NumberSequences] " & _
                          "WHERE [Name]='" & NumberingName.ToString & "'"
        Dim da As New SqlDataAdapter(cmd)
        Dim dt As New DataTable
        da.Fill(dt)
        Dim sId As Guid = Nothing
        Dim num As Integer = 0
        Dim padding As Integer = 0
        Dim prefix As String = ""
        Dim suffix As String = ""
        If dt.Rows.Count < 1 Then
            cmd.CommandText = "INSERT INTO [SYS_NumberSequences] " & _
                              "VALUES(NEWID(),'" & NumberingName.ToString & "',1,NULL,NULL,6,NULL,GETDATE())"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "SELECT * FROM [SYS_NumberSequences] " & _
                              "WHERE [Name]='" & NumberingName.ToString & "'"
            da.Fill(dt)
        End If
        If dt.Rows.Count > 0 Then
            sId = dt.Rows(0)("NumberSequenceId")
            num = dt.Rows(0)("Value")
            If IsDBNull(dt.Rows(0)("Prefix")) Then
                prefix = ""
            Else
                prefix = dt.Rows(0)("Prefix")
            End If
            If IsDBNull(dt.Rows(0)("Suffix")) Then
                suffix = ""
            Else
                suffix = dt.Rows(0)("Suffix")
            End If
            padding = dt.Rows(0)("Padding")
            If padding > 0 Then
                For i As Integer = 0 To padding - 1
                    res &= "0"
                Next
            End If
            res = num.ToString(res)
            res = prefix.Trim & res.Trim & suffix.Trim
            If Increment Then
                cmd.CommandText = "UPDATE [SYS_NumberSequences] SET [Value]=" & num + 1 & " " & _
                                  "WHERE [NumberSequenceId]='" & sId.ToString & "'"
                cmd.ExecuteNonQuery()
            End If
        End If
        Return res

    End Function
    Public Function Get_SequenceNo(NumberingName As SequenceNumeringNamesEnum, Optional Increment As Boolean = False) As String

        Dim res As String = ""
        Using con As New SqlConnection(Utility.GetConnectionString)
            con.Open()
            Dim trans As SqlTransaction = con.BeginTransaction
            res = Get_SequenceNo(con, trans, NumberingName, Increment)
            trans.Commit()
            con.Close()
        End Using
        Return res

    End Function

#End Region

#Region "Shared Utility Functions"
    Shared Function DataBaseConnectivityTest() As Boolean
        Return Utility.DataBaseConnectivityTest()
    End Function
    Shared Function GetConnectionString() As String
        Return Utility.GetConnectionString()
    End Function
    Shared Sub SetConnectoinString(ConnectionString As String)
        Utility.SetConnectoinString(ConnectionString)
    End Sub
    Shared Function DateFormatDisplay(ByVal sampleDate As DateTime) As String
        Return Utility.DateFormatDisplay(sampleDate)
    End Function
    Shared Function DateFormatSql(ByVal sampleDate As DateTime) As String
        Return Utility.DateFormatSql(sampleDate)
    End Function
    Shared Function FileNameFormatSqlUpdates(VersionNumber As Integer) As String
        Return Utility.FileNameFormatSqlUpdates(VersionNumber)
    End Function

#End Region

End Class


Public Enum SequenceNumeringNamesEnum
    CustomerNumberSequence
    ServiceOrderNumberSequence
End Enum